﻿using QWManage.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QWManage.DAL
{
    public class CustomerDAL : SqlDAL
    {
        public CustomerDAL()
        {
            TableName = "QW_Customers";
        }

        public CustomerInfo GetCustomerInfoByYiYeUserId(string yiyeUserId)
        {
            CustomerInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where yiyeUserId=@yiyeUserId";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@yiyeUserId", yiyeUserId));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetCustomer(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }
        public void Add(IEnumerable<CustomerInfo> customerInfos)
        {
            foreach (CustomerInfo obj in customerInfos)
            {
                if (GetCustomerInfo(obj.Exuserid) == null) Insert(obj);
                else Update(obj);
            }
        }

        private bool Update(CustomerInfo obj)
        {
            string sql = $"update {TableName} set " +
                $"Name=@Name, " +
                $"Avatar=@Avatar, " +
                $"Type=@Type, " +
                $"Unionid=@Unionid, " +
                $"F_UserID=@F_UserID, " +
                $"F_UserName=@F_UserName, " +
                $"F_Rmeark=@F_Rmeark, " +
                $"F_Desrciption=@F_Desrciption, " +
                $"F_CreateTime=@F_CreateTime, " +
                $"F_Oper_UserID=@F_Oper_UserID, " +
                $"F_Tag_IDS=@F_Tag_IDS " +
                $"where Exuserid=@Exuserid";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Exuserid", SqlNull(obj.Exuserid));
                cmd.Parameters.AddWithValue("@Name", SqlNull(obj.Name));
                cmd.Parameters.AddWithValue("@Avatar", SqlNull(obj.Avatar));
                cmd.Parameters.AddWithValue("@Type", SqlNull(obj.Type));
                cmd.Parameters.AddWithValue("@Unionid", SqlNull(obj.Unionid));
                cmd.Parameters.AddWithValue("@F_UserID", SqlNull(obj.F_UserID));
                cmd.Parameters.AddWithValue("@F_UserName", SqlNull(obj.F_UserName));
                cmd.Parameters.AddWithValue("@F_Rmeark", SqlNull(obj.F_Rmeark));
                cmd.Parameters.AddWithValue("@F_Desrciption", SqlNull(obj.F_Desrciption));
                cmd.Parameters.AddWithValue("@F_CreateTime", SqlNull(obj.F_CreateTime));
                cmd.Parameters.AddWithValue("@F_Oper_UserID", SqlNull(obj.F_Oper_UserID));
                cmd.Parameters.AddWithValue("@F_Tag_IDS", SqlNull(obj.F_Tag_IDS));
                conn.Open();
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }
        private bool Insert(CustomerInfo obj)
        {
            string sql = $"insert into {TableName} " +
                $"(Exuserid,Name,Avatar,Type,Unionid,F_UserID,F_UserName,F_Rmeark,F_Desrciption,F_CreateTime,F_Oper_UserID,F_Tag_IDS) " +
                $"values" +
                $"(@Exuserid,@Name,@Avatar,@Type,@Unionid,@F_UserID,@F_UserName,@F_Rmeark,@F_Desrciption,@F_CreateTime,@F_Oper_UserID,@F_Tag_IDS)";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Exuserid", SqlNull(obj.Exuserid));
                cmd.Parameters.AddWithValue("@Name", SqlNull(obj.Name));
                cmd.Parameters.AddWithValue("@Avatar", SqlNull(obj.Avatar));
                cmd.Parameters.AddWithValue("@Type", SqlNull(obj.Type));
                cmd.Parameters.AddWithValue("@Unionid", SqlNull(obj.Unionid));
                cmd.Parameters.AddWithValue("@F_UserID", SqlNull(obj.F_UserID));
                cmd.Parameters.AddWithValue("@F_UserName", SqlNull(obj.F_UserName));
                cmd.Parameters.AddWithValue("@F_Rmeark", SqlNull(obj.F_Rmeark));
                cmd.Parameters.AddWithValue("@F_Desrciption", SqlNull(obj.F_Desrciption));
                cmd.Parameters.AddWithValue("@F_CreateTime", SqlNull(obj.F_CreateTime));
                cmd.Parameters.AddWithValue("@F_Oper_UserID", SqlNull(obj.F_Oper_UserID));
                cmd.Parameters.AddWithValue("@F_Tag_IDS", SqlNull(obj.F_Tag_IDS));
                conn.Open();
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }
        public Dictionary<string, string> GetAvatarDic()
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            using (SqlConnection conn = new SqlConnection(SQLCON))
            {
                string sql = "select Avatar,ExuserID from [QW_Customers] where Avatar is not null";
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string avatar = ReadString(reader, "Avatar");
                        string exuserid = ReadString(reader, "ExuserID");
                        if (string.IsNullOrEmpty(avatar) || string.IsNullOrEmpty(exuserid))
                            continue;
                        if (!dic.ContainsKey(avatar))
                            dic.Add(avatar, exuserid);
                    }
                }
            }
            return dic;
        }
        public List<string> GetExuserIDs(string userid)
        {
            HashSet<string> hashset = new HashSet<string>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = "select ExuserID from [QW_Users_Customers] where UserID=@UserID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@UserID", userid));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string exuserid = reader.GetString(0);
                        if (!hashset.Contains(exuserid))
                            hashset.Add(exuserid);
                    }
                }
                return hashset.ToList();
            }
            finally
            {
                conn.Close();
            }
        }

        public List<CustomerInfo> GetCustomerInfos(List<string> exuserids)
        {
            List<CustomerInfo> list = new List<CustomerInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                foreach (string exuserid in exuserids)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = $"select * from {TableName} where ExuserID=@ExuserID";
                    cmd.Parameters.Add(new SqlParameter("@ExuserID", exuserid));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            CustomerInfo customerInfo = GetCustomer(reader);
                            list.Add(customerInfo);
                        }
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<CustomerInfo> GetCustomerInfos()
        {
            List<CustomerInfo> list = new List<CustomerInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = "select * from " + TableName;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        CustomerInfo customerInfo = GetCustomer(reader);
                        list.Add(customerInfo);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        public CustomerInfo GetCustomerInfo(string exuserid)
        {
            CustomerInfo customer = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where Exuserid=@Exuserid";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@ExuserID", exuserid));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        customer = GetCustomer(reader);
                    }
                }
                return customer;
            }
            finally
            {
                conn.Close();
            }
        }

        private CustomerInfo GetCustomer(SqlDataReader reader)
        {
            CustomerInfo obj = new CustomerInfo();
            obj.Exuserid = ReadString(reader, "Exuserid");
            obj.Name = ReadString(reader, "Name");
            obj.Avatar = ReadString(reader, "Avatar");
            obj.Type = (int)reader["Type"];
            obj.Unionid = ReadString(reader, "Unionid");

            obj.F_UserID = ReadString(reader, "F_UserID");
            obj.F_Rmeark = ReadString(reader, "F_Rmeark");
            obj.F_Desrciption = ReadString(reader, "F_Desrciption");
            obj.F_CreateTime = ReadIntNull(reader, "F_CreateTime");
            obj.F_Add_Way = ReadIntNull(reader, "F_Add_Way");
            obj.F_Oper_UserID = ReadString(reader, "F_Oper_UserID");
            obj.F_Tag_IDS = ReadString(reader, "F_Tag_IDS");
            return obj;
        }

        public void UpdateQWGroupStatus(string exuserid, int status)
        {
            using (SqlConnection conn = new SqlConnection(SQLCON))
            {
                conn.Open();
                string sql = "update QW_Customers set W_isInQW=@status where exuserid=@exuserid";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@status", status);
                cmd.Parameters.AddWithValue("@exuserid", exuserid);
                cmd.ExecuteNonQuery();
            }
        }

        public List<CustomerInfo> Search(string userId, int fromTime, int toTime)
        {
            // 步骤3：找出该企微在对应的时间点内的数据进行匹配
            //SELECT
            //[Exuserid]
            //,[Name]
            //,[F_CreateTime]
            // FROM[qwmanage].[dbo].[QW_Customers]
            // where[F_UserID] = 'HuangFengSong_2'
            // and[F_CreateTime] 1637038281 秒级
            // 跟[Records]中的时间字段在5秒内的数据。
            List<CustomerInfo> customers = new List<CustomerInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where" +
                    $" F_UserID=@userId and F_CreateTime >=@fromTime and F_CreateTime<=@toTime";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@userId", userId));
                cmd.Parameters.Add(new SqlParameter("@fromTime", fromTime));
                cmd.Parameters.Add(new SqlParameter("@toTime", toTime));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var customer = GetCustomer(reader);
                        customers.Add(customer);
                    }
                }
                return customers;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
